package com.eascs.web.o2o.scheduler.service;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.stereotype.Service;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.DateUtils;
import com.eascs.web.o2o.scheduler.dao.CrmDao;
import com.eascs.web.o2o.scheduler.dao.ServiceDao;
import com.eascs.web.o2o.scheduler.entity.CompensatoryEntity;
import com.eascs.web.o2o.scheduler.entity.O2oOverdueReport;
import com.eascs.web.o2o.scheduler.util.Constant;
import com.eascs.web.o2o.scheduler.util.DateUtil;

import jodd.util.StringUtil;

@Service
public class SynchroAfterLoanForService {

	private final Logger logger = LoggerFactory.getLogger(SynchroAfterLoanForService.class);

	@Inject
	CrmDao crmDao;
	@Inject
	ServiceDao serviceDao;

	private static final int end_year = 2018;

	/**
	 * 
	 * @Title: synchroAfterLoanForCustomer
	 * @Description: 按照客户维度统计
	 * @author user
	 * @return void
	 * @throws @date
	 *             2017年3月1日 上午11:11:36
	 */
	public void synchroAfterLoanForCustomer() {

		// 1.导入客户信息：crm-service
		String sql1 = " SELECT t.loan_id ,t.document_no ,t.document_type from o2o_loan_user t where t.spouse_id is null and t.sponsor_type is NULL group by t.document_type,t.document_no ";
		try {
			logger.info("begin：导入客户信息");
			loadCrmToService(sql1);
			logger.info("end:导入客户信息");
		} catch (Exception e) {
			logger.error("导入客户信息异常:[{}]", e.getMessage());
		}
		// 2.放款次数 service
		String sql2 = " select customer_id , idcard , idcard_type  from o2o_after_loan_for_customer ";
		String sql3 = "update o2o_after_loan_for_customer set loan_num = ? where customer_id = ? ";
		try {
			logger.info("begin：更新客户放款次数");
			updateCustomerLoanNum(sql2, sql3);
			logger.info("end：更新客户放款次数");
		} catch (Exception e) {
			logger.error("更新放款次数异常[{}]", e.getMessage());
		}
		// 3.更新第一放款时间
		String sql4 = "select customer_id, idcard_type , idcard  from  o2o_after_loan_for_customer ";
		String sql5 = "update o2o_after_loan_for_customer set the_first_date  =  ? where customer_id = ?  ";
		try {
			logger.info("begin:更新客户第一次放款时间");
			updateCustomerFirstLoanTime(sql4, sql5);
			logger.info("end:更新客户第一次放款时间");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("Error：更新第一次放款时间异常：[{}]", e.getMessage());
		}

	}

	/**
	 * 
	 * @Title: synchroAfterLoanForBillCode
	 * @Description: 按照贷款单维度统计
	 * @author user
	 * @return void
	 * @throws @date
	 *             2017年3月1日 上午11:11:59
	 */
	public void synchroAfterLoanForBillCode() {

		// 同步所有已放款的单
		String sql = " select o.bill_code ,b.loan_date, b.loan_term,b.loan_amount,b.loan_bank,o.wf_type from o2o_loanapply o left join o2o_bank_loan_data b on o.bill_code =b.bill_code left join  o2o_after_loan_for_billcode l on  o.bill_code =l.bill_code where o.loan_flg ='Y' and o.islock ='N' and  o.loan_type in ('BK', 'BP','BP_JX','SG','YC','SC') ";
		try {
			logger.info("begin:同步所有已放款的单......");
			loadLoanData(sql);
			logger.info("end:同步所有已放款的单......");
		} catch (Exception e) {
			logger.error("Error：同步所有已放款的单失败！！！", e);
		}
		// 同步放款余额,截止去年年末余额

		/**
		 * 2017.04.17 产品确认： 1.银行数据处理分两类：中行，非中行
		 * 中行：包含逾期（o2o_overdue_report）（也就是会导逾期数据）和代偿（o2o_ea_loan_data）（代偿表中分本金代偿
		 * ，利息代偿）。 非中行（国安，建行。。。）：只有代偿，只要逾期就会代偿。
		 * 2.主表（o2o_loanapply）只有代偿和逾期，没有本金和利息的细分。
		 * 3.当本金逾期，利息逾期，利息代偿，在主表都是逾期状态,当本金代偿主表才为代偿状态。
		 * 对于中行，当状态为本金逾期，利息逾期，在逾期表中有数据。当状态为代偿时，在代偿表中有数据。
		 * 非中行，只会在代偿表中有数据，因为它不会导逾期数据。
		 * 
		 * //利息逾期，利息代偿：有可能是本金逾期后，也可能是本金逾期前。
		 */

		String sql1 = " select  t.bill_code,t.loan_amount,t.wf_status,ADDDATE(t.loan_date,INTERVAL t.loan_term MONTH) as end_date,l.pay_date as dc_date,o.settle_date jq_date,t.loan_date,o.repay_mentway,l.loan_type,r.status from o2o_after_loan_for_billcode t  left join o2o_loanapply o on t.bill_code=o.bill_code  left join o2o_ea_loan_data l on t.bill_code =l.bill_code left join o2o_overdue_report r on t.bill_code=r.billcode and  r.isnew ='Y'  order by l.loan_type desc ";
		try {
			logger.info("begin:同步放款余额......");
			updateOutstandingOfDeposits(sql1);
			logger.info("end:同步放款余额......");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("Error：同步所有已放款的单异常:[{}]", e.getMessage());
		}
		// 逾期数据
//		try {
//			logger.info("begin:同步逾期余额......");
//			updateOverdue();
//			logger.info("end:同步逾期余额......");
//		} catch (Exception e) {
//			e.printStackTrace();
//			logger.error("Error：同步逾期的单异常:[{}]", e.getMessage());
//		}
//		// 代偿数据
//		try {
//			logger.info("begin:同步代偿......");
//			updateCompensatory();
//			logger.info("end:同步代偿余额......");
//		} catch (Exception e) {
//			e.printStackTrace();
//			logger.error("Error：同步代偿的单异常:[{}]", e.getMessage());
//		}
	}

	/**
	 * 计算上年逾期和今年的逾期金额
	 * 
	 * @param overdueSql
	 */
	public void updateOverdue() {
		logger.info("开始更新上年逾期和今年的逾期金额");
		int count = 0;
		int index = 0;
		String sql = "SELECT * FROM o2o_after_loan_for_billcode ";
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				try {
					String billCode = objs[1] == null ? null : (String) objs[1];
					String overdueSql = "SELECT * FROM o2o_overdue_report_interim t where t.timeframe BETWEEN ? AND ? AND t.billcode=?  GROUP BY t.billcode";
					O2oOverdueReport overdueLastReport=serviceDao.findEntity(overdueSql, O2oOverdueReport.class, DateUtil.getYeastyearFrist(), DateUtil.getYeastyearLast(), billCode);
					O2oOverdueReport overdueCurrReport=serviceDao.findEntity(overdueSql, O2oOverdueReport.class, DateUtil.getCurrYearFirst(), DateUtil.getCurrYearLast(), billCode);
					if(overdueCurrReport!=null){
						serviceDao.excuteSql("update o2o_after_loan_for_billcode  set current_overdue_balance = ?  where bill_code =? ", overdueCurrReport.getDefaultamount(), billCode);
						count++;
					}
					if(overdueLastReport!=null){
						serviceDao.excuteSql("update o2o_after_loan_for_billcode  set  last_overdue_balance=?  where bill_code =? ",overdueLastReport.getDefaultamount(), billCode);
						count++;
					}
				} catch (Exception e) {
					logger.error("单据:[{}]更新逾期余额异常[{}]", objs[0].toString(), e.getMessage());
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("更新上年逾期和今年的逾期金额条数:[{}]", count);

	}

	/**
	 * 计算上年代偿和今年代偿金额
	 * 
	 * @param compensatorySql
	 */
	public void updateCompensatory() {
		logger.info("开始更新上年代偿和今年代偿金额");
		int count = 0;
		int index = 0;
		String sql = "SELECT * FROM o2o_after_loan_for_billcode ";
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				try {
					String billCode = objs[1] == null ? null : (String) objs[1];
					if(billCode.equals("O2O1504-0023")){
						System.out.println("11");
					}
					//当前代偿余额
					String currentCompensatorySql="SELECT SUM(IFNULL(t.pay_amount, 0))-SUM(IFNULL(t.payment_amount, 0))   AS amount FROM  o2o_ea_loan_data t"
							 +" WHERE t.loan_type = 'DC' AND t.bill_code=? ";
					CompensatoryEntity currCompensatoryEntity=serviceDao.findEntity(currentCompensatorySql, CompensatoryEntity.class,billCode);
					
					if(currCompensatoryEntity!=null && currCompensatoryEntity.getAmount()!=null){
						serviceDao.excuteSql("update o2o_after_loan_for_billcode  set current_compensatory_balance = ?  where bill_code =? ",currCompensatoryEntity.getAmount().compareTo(new BigDecimal(0))>0?currCompensatoryEntity.getAmount():new BigDecimal(0), billCode);
					}
					
					//去年年末代偿余额
						//代偿金额
					String compensatorySql="SELECT SUM(IFNULL(t.pay_amount, 0))  AS amount FROM  o2o_ea_loan_data t"
										 +" WHERE t.loan_type = 'DC' AND t.pay_date < ? and  t.bill_code=? ";
						//代偿回款金额
					String paymentAmountSql="select SUM(IFNULL(this_payment_principal, 0)) as thisPaymentPrincipal from o2o_badloan_record "
							+ " where is_delete='N' and flow_status='CLO' and this_payment_date  < ?  AND  bill_code = ? ";
					List<Object[]> list1 = serviceDao.findParams(compensatorySql,  DateUtil.getYeastyearLast(), billCode);
					List<Object[]> list2 = serviceDao.findParams(paymentAmountSql,  DateUtil.getYeastyearLast(), billCode);
					BigDecimal payAmount = new BigDecimal(0);
					BigDecimal paymentAmount = new BigDecimal(0);
					if(list2!=null&&list2.size()>0){
						Object[] o = list2.get(0);
						paymentAmount = (BigDecimal) (o[0]==null? new BigDecimal(0):o[0]);
					}
					if(list1!=null && list1.size()>0){
						Object[] o = list1.get(0);
						payAmount =  (BigDecimal)(o[0]==null? new BigDecimal(0):o[0]);
						serviceDao.excuteSql("update o2o_after_loan_for_billcode  set last_compensatory_balance = ?  where bill_code =? ",payAmount.subtract(paymentAmount).compareTo(new BigDecimal(0))>0?payAmount.subtract(paymentAmount):new BigDecimal(0), billCode);
						count++;
					}
				} catch (Exception e) {
					e.getMessage();
					logger.error("单据:[{}],上年代偿和今年代偿金额异常[{}]", objs[0].toString(), e);
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("更新上年代偿和今年代偿金额条数:[{}]", count);
	}
	
	
	
	public void updateOutstandingOfDeposits(String sql) {
		logger.info("开始更新放款余额");
		int index = 0;
		int total = 0;
		int count = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			logger.info("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				try {
					String billCode = objs[0] == null ? null : (String) objs[0];
					BigDecimal loanAmount = objs[1] == null ? new BigDecimal(0.0) : (BigDecimal) objs[1];// 放款金额
					String repayStatus = objs[2] == null ? null : (String) objs[2];// 主单状态
					Date repayDate = objs[3] == null ? null : (Date) objs[3];// 到期日
					// 这两个字段是为了算去年年末余额加入的：在17年去算16到年末余额，就需要判断现在状态为代偿或还款完成的，是否为提前代偿，提前还款完成。
					Date dcDate = objs[4] == null ? null : (Date) objs[4];// 代偿时间
					Date jqDate = objs[5] == null ? null : (Date) objs[5];// 结清时间
					Date loanDate = objs[6] == null ? null : (Date) objs[6];// 放款时间
					String repayMentway = objs[7] == null ? null : (String) objs[7];// 还款方式
					String dcStatus = objs[8] == null ? null : (String) objs[8];// 代偿状态
					String yqStatus = objs[9] == null ? null : (String) objs[9];// 逾期状态
					BigDecimal outstandingOfDeposits = refundBlanceAmount(billCode, loanAmount, repayStatus, repayDate, dcDate, jqDate, loanDate, repayMentway,dcStatus,yqStatus,0);// 更新放款余额
					BigDecimal lastYearBanlanceAmount = refundBlanceAmount(billCode, loanAmount, repayStatus, repayDate, dcDate, jqDate, loanDate, repayMentway,dcStatus,yqStatus, 1);// 更新截止去年年末余额
					serviceDao.excuteSql("update o2o_after_loan_for_billcode  set outstanding_of_deposits = ?, last_year_banlance_amount=?  where bill_code =? ", outstandingOfDeposits, lastYearBanlanceAmount, billCode);
					count++;
				} catch (Exception e) {
					logger.error("单据:[{}]更新放款余额异常[{}]", objs[0].toString(), e.getMessage());
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("更新放款余额的总条数:[{}]", count);

	}

	private BigDecimal refundBlanceAmount(String billCode, BigDecimal loanAmount, String repayStatus, Date repayDate, Date dcDate, Date jqDate, Date loanDate, String repayMentway, String dcStatus,String yqStatus,int type) {
		BigDecimal amount = new BigDecimal(0);//正常余额
		/**
		 * 正常余额：
		 *       依据：银行逾期数据，代偿数据，单据状态（注意：现在系统单据不做利息代偿，如果利息逾期了，要么上传的逾期报表，要么直接本金代偿）
		 */
		if (type == 0) {
			//1.结清
				// 1.1 ，还款状态为还款完成的置为0
				if (!StringUtils.isEmpty(repayStatus) && repayStatus.equals("HN")) {
					return amount;
				}
				// 1.2，代偿状态直接置0 （代偿分为两种：本金代偿，利息代偿），如果是利息代偿，主表状态会置为逾期。
				if (!StringUtils.isEmpty(repayStatus) && repayStatus.equals("DC")) {
					return amount;
				}
			//2.未结清
				// 等额本金、等额本息、按月付息，到期还本
				String sql = "select residual_principal from o2o_payments_plan where is_del='N' and bill_code =? and payments_date < now() order by  payments_date desc limit 1 ";
				Object[] o = (Object[]) serviceDao.findEntity(sql, Object[].class, billCode);
				if (o == null) {//2.1未生成还款计划
					amount = loanAmount;//不在还款计划中（还未生产还款计划），取放款金额为正常余额
				} else {//2.2已生成还款计划
						Date yestoday = new Date(new Date().getTime());//到期日当天算未到期
						if(yestoday.after(repayDate)){//到期日已到
							//2.2.1导入了逾期数据
							if(!StringUtils.isEmpty(yqStatus)){//到期了，且有逾期数据，余额置为0
								return amount;
							}if(!StringUtils.isEmpty(dcStatus) && dcStatus.equals("LXDC")){//没有做本金代偿，只做了利息代偿，且没有导入逾期数据，认为已结清
								return  amount;//取放款金额为正常余额（注意：如果本金代偿流程未发起/逾期报表未导入，只是线下还款就会出现数据问题）
							}else{
								/**
								 * 1.状态为还款中：如果利息逾期或利息代偿情况下，导致主单状态为逾期，也是取还款计划中当前时间前一期的剩余本金作为正常余额
								 * 2.到期日已到期：如果已到期，但是未上传逾期数据，则取还款计划中当前时间前一期的剩余本金作为正常余额
								 */
								//2.2.2未导入逾期数据（已到期，未导逾期数据，认为是还款中，按还款计划取）
								amount = (BigDecimal) o[0];
							}
						}else{
							//未到期（状态是还款中和逾期都按还款计划取），不管状态了（因为已到期的既没有还款完成，也没有本金代偿，那么只可能是利息逾期（未到期不会本金逾期，还有现在不导入逾期报表），当时利息逾期的时候，正常余额还是按还款计划取值）
							amount = (BigDecimal) o[0];
						}
			   }
			  return amount;
		} else {// 截止去年年末余额
				// 1.到期日在去年年末之前，余额置为0
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			int endYear = Integer.parseInt(sdf.format(repayDate));// 到期日的年份
			if (endYear < end_year) {
				return amount;
			}
			// 2.放款日不是去年，余额置为0(今年放款的单，不能计到去年年末余额中)
			int beginYear = Integer.parseInt(sdf.format(loanDate));// 到期日的年份
			if (beginYear > end_year - 1) {
				return amount;
			}
			// 3.提前代偿(17到期，在16年代偿) ，余额置为0
			if (dcDate != null) {
				int dc = Integer.parseInt(sdf.format(dcDate));
				if (!StringUtils.isEmpty(repayStatus) && repayStatus.equals("DC") && dc < end_year) {
					return amount;
				}
			}

			// 4.提前还款完成(17到期，在16还款完成)，余额置为0
			if (jqDate != null) {
				int jq = Integer.parseInt(sdf.format(jqDate));
				if (!StringUtils.isEmpty(repayStatus) && repayStatus.equals("HN") && jq < end_year) {
					return amount;
				}
			}
			// 5.还款方式为按月还息，到期还本时，未到期就直接取放款金额
			/*
			 * if (!StringUtils.isEmpty(repayMentway) &&
			 * repayMentway.equals("RMT")) { return loanAmount; }
			 */
			// 6.到期日在今年，没有提前代偿，也没有提前还款完成，通过还款计划计算正常余额
			String sql = "select residual_principal from  o2o_payments_plan where is_del='N' and bill_code=? and  year(payments_date) <= year(date_sub(now(),interval 1 year)) order by payments_date desc limit 1 ";
			Object[] o = (Object[]) serviceDao.findEntity(sql, Object[].class, billCode);
			if (o == null) {
				amount = loanAmount;
			} else {
				amount = (BigDecimal) o[0];
			}
			return amount;

		}

	}

	public static void main(String[] args) {
		System.out.println(new BigDecimal(2).compareTo(new BigDecimal(4)));

	}

	public void loadLoanData(String sql) {
		logger.info("开始插入新贷款单");
		int index = 0;
		int total = 0;
		int count = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = this.serviceDao.findDataWithPageForService(sqlPageQuery);
			logger.info("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				try {
					int num = serviceDao.countMysql("select count(1) from o2o_after_loan_for_billcode where bill_code =?", obj[0]);
					if (num <= 0) {
						serviceDao.excuteSql("insert into o2o_after_loan_for_billcode(id,bill_code,loan_date,loan_term,loan_amount,loan_bank,wf_status,create_date) values (uuid(),?,?,?,?,?,?,now()) ", obj[0], obj[1], obj[2], obj[3], obj[4], obj[5]);
						count++;
						logger.info("新插入贷款单:[{}]", obj[0]);
					} else {
						serviceDao.excuteSql("update o2o_after_loan_for_billcode set loan_date=?,loan_term=?,loan_amount=?,loan_bank=?,wf_status=?,update_date=now() where bill_code = ? ", obj[1], obj[2], obj[3], obj[4], obj[5], obj[0]);
					}
				} catch (DataIntegrityViolationException e) {
					logger.error("贷款单：[{}]插入异常[{}]", obj[0], e.getMessage());
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("插入新贷款单的条数:[{}]................", count);
	}

	public void updateCustomerFirstLoanTime(String sql, String updateSql) {
		logger.info("开始更新第一次放款");
		int index = 0;
		int count = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				String first_date = "";
				try {
					String sql1 = " select min(t.payment_time)  as first_date from o2o_loanapply t where t.loan_flg='Y' and t.loan_id = ? ";
					List<Object[]> o = serviceDao.findParams(sql1, objs[0]);
					if (o != null && o.size() > 0) {
						Date date = (Date) o.get(0)[0];
						if (date != null) {
							first_date = DateUtils.formateStandTime(date);
						}
					}
					if (StringUtil.isNotBlank(first_date)) {
						serviceDao.excuteSql(updateSql, first_date, objs[0]);
						count++;
					}
				} catch (Exception e) {
					logger.error("更新客户：[{}]第一次放款时间异常", objs[0], e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("更新第一次放款条数:[{}]", count);
	}

	public void updateCustomerLoanNum(String sql, String updateSql) {
		logger.info("开始更新贷款次数");
		int index = 0;
		int total = 0;
		int count = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			logger.info("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				int loanNum = 0;
				try {
					loanNum = serviceDao.countMysql("select count(1) from o2o_loanapply t where t.loan_flg ='Y' and t.loan_id = ?", objs[0]);
					serviceDao.excuteSql(updateSql, loanNum, objs[0]);
					count++;
				} catch (Exception e) {
					logger.error("更新客户：[{}] 的放款次数发生异常[{}]", objs[0], e.getMessage());
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("更新贷款次数总条数：[{}]", count);
	}

	public void loadCrmToService(String sql) {
		int index = 0;
		int total = 0;
		int count = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			logger.info("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				try {
					int num = serviceDao.countMysql("select count(1) from o2o_after_loan_for_customer where customer_id=?", obj[0]);
					if (num <= 0) {
						serviceDao.excuteSql("insert into o2o_after_loan_for_customer(id,customer_id,idcard,idcard_type,update_date) values (uuid(),?,?,?,now()) ", obj[0], obj[1], obj[2]);
						logger.info("插入客户信息:[{}]", obj[0].toString());
						count++;
					}
				} catch (Exception e) {
					logger.error("数据库异常[{}]", e.getMessage());
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("一共插入新客户的条数:[{}]", count);
	}

	public void synchBalanceHis() {
		logger.info("开始同步余额数据，历史存档");
		String sql = " select t.bill_code,t.ltd,o.loan_amount from o2o_loanapply t inner join  o2o_bank_loan_data o  on t.bill_code =o.bill_code where t.loan_flg ='Y' and islock ='n' ";
		int index = 0;
		int count = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				try {
					Object[] obj = pageResult.getData().get(i);
					count++;
					// 插入数据
					serviceDao.excuteSql("insert into o2o_balance_his(id,bill_code,ltd,loan_amount,create_date) values (uuid(),?,?,?,now())", obj[0], obj[1], obj[2]);
					// 更新正常余额
					List<Object[]> list1 = serviceDao.findParams("select  outstanding_of_deposits from o2o_after_loan_for_billcode where bill_code = ?", obj[0]);
					if (list1 != null && list1.size() > 0) {
						Object[] obj1 = list1.get(0);
						serviceDao.excuteSql("update o2o_balance_his set outstanding_of_deposits = ?  where bill_code =? ", obj1[0], obj[0]);
					}
					// 当前本金逾期金额
					List<Object[]> list2 = serviceDao.findParams("select defaultcapital from o2o_overdue_report where billcode = ?", obj[0]);
					if (list2 != null && list2.size() > 0) {
						Object[] obj2 = list2.get(0);
						serviceDao.excuteSql("update o2o_balance_his set defaultcapital = ?  where bill_code =? ", obj2[0], obj[0]);
					}
					// 代偿金额总额
					List<Object[]> list3 = serviceDao.findParams("select sum(pay_amount) from o2o_ea_loan_data where bill_code = ?", obj[0]);
					if (list3 != null && list3.size() > 0) {
						Object[] obj3 = list3.get(0);
						serviceDao.excuteSql("update o2o_balance_his set compensation = ?  where bill_code =? ", obj3[0], obj[0]);
					}
					// 当前代偿欠还金额
					List<Object[]> list4 = serviceDao.findParams("select now_arrears from o2o_badloan_record where bill_code = ?", obj[0]);
					if (list4 != null && list4.size() > 0) {
						Object[] obj4 = list4.get(0);
						serviceDao.excuteSql("update o2o_balance_his set compensatory_balance = ?  where bill_code =? ", obj4[0], obj[0]);
					}
				} catch (Exception e) {
					logger.info("同步余额数据，历史存档,发生异常[{}]", e.getMessage());
				}

			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("结束同步余额数据，历史存档,一共插入[{}]条数据", count);
	}

	public void synchBalanceHisToday() {
		String sql = "select bill_code ,outstanding_of_deposits  from o2o_balance_his where  update_date = CURDATE()";
		int index = 0;
		logger.info("开始实时更新放款余额历史存档中当天的数据");
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPage(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				try {
					serviceDao.excuteSql("update o2o_balance_his  set outstanding_of_deposits = ?  where bill_code = ? and update_date =CURDATE()", obj[1], obj[0]);
				} catch (Exception e) {
					logger.error("实时更新放款余额历史存档中当天的数据数据库异常[{}]", e.getMessage());
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("结束实时更新放款余额历史存档中当天的数据");
	}

	public void synchReLoan() {

		String sql = "select t.bill_code,(select count(1) from o2o_loanapply l where l.loan_id = t.loan_id and l.payment_time <= t.payment_time ) loan_num from  o2o_loanapply t  where t.loan_flg ='Y' ";

		List<Object[]> list = serviceDao.findObjectsList(sql);
		logger.info("续贷更新记录数{}=============", list.size());
		for (int i = 0; i < list.size(); i++) {
			Object[] b = list.get(i);
			Long num = (Long) b[1];
			serviceDao.excuteSql("update o2o_loanapply set is_continue_loan= 'N' where bill_code=? ", b[0]);
			if (num > 1) {
				serviceDao.excuteSql("update o2o_loanapply set is_continue_loan = 'Y' where bill_code=? ", b[0]);
			}

		}
	}

}
